package com.study.manage.dal;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import com.study.manage.domain.EvaluateJury;
import com.study.manage.domain.enumm.LiveDieState;

public interface EvaluateJuryRepository extends PagingAndSortingRepository<EvaluateJury, Long> {

    List<EvaluateJury> findByEvaluateIdAndState(Long evaluateId, LiveDieState state);

    int countByEvaluateIdAndState(Long evaluateId, LiveDieState state);

    int countByEvaluateIdAndTeacherIdAndState(Long evaluateId, Long userId, LiveDieState state);

    int countByTeacherIdAndState(Long userId, LiveDieState state);

    @Query("select t.evaluateId from EvaluateJury t where t.teacherId=:teacherId and t.state=0")
    List<Long> findEvaluateIdByTeacherIdAndState(@Param("teacherId") Long teacherId);

    @Query("select t from EvaluateJury t where t.teacherId=:teacherId and t.state=0")
    Page<EvaluateJury> findEvaluateByTeacherId(@Param("teacherId") Long teacherId,
            Pageable pageable);

    @Query("select e from EvaluateJury e where e.state=0 and e.evaluateId=:evaluateId")
    Page<EvaluateJury> getTableData(@Param("evaluateId") Long evaluateId, Pageable pageable);

    @Query("select e from EvaluateJury e where e.state=0 and e.evaluateId=:evaluateId")
    List<EvaluateJury> getByEvaluateId(@Param("evaluateId") Long evaluateId);

    @Query("select e from EvaluateJury e,Blog b,EvaluateApply ea,BlogComment bc where ea.evaluateId=:evaluateId"
            + " and b.extId=ea.id and b.type=6 and b.id=bc.blogId and e.teacherId=bc.userId and b.state=0 and bc.state=0 and ea.state=0 "
            + " and e.state=0 and e.evaluateId=:evaluateId group by e.id order by count(e.id) desc")
    Page<EvaluateJury> getTableDataDesc(@Param("evaluateId") Long evaluateId, Pageable pageable);

    @Query(nativeQuery = true,
            value = "select count(1) from sa_evaluate_jury where state=0 and evaluate_id =?1")
    int getJudgesNumber(Long id);

    @Query(nativeQuery = true, value = "SELECT count(1) FROM sa_blog_comment "
            + "INNER JOIN sa_blog ON sa_blog_comment.blog_id = sa_blog.id and sa_blog.typee = 6 and sa_blog.state=0 "
            + "INNER JOIN sa_evaluate_apply ON sa_evaluate_apply.id = sa_blog.ext_id and sa_evaluate_apply.evaluate_id= ?1 "
            + "where sa_blog_comment.state=0 and sa_blog_comment.if_teacher_send=0 and (sa_blog_comment.user_id = ?2 or ?2 is null)")
    int getCommentNumber(Long evaluateId, Long userId);

    @Query(nativeQuery = true,
            value = "SELECT COUNT(1) FROM(SELECT sa_blog.user_id FROM sa_blog_comment "
                    + "INNER JOIN sa_blog ON sa_blog_comment.blog_id = sa_blog.id and sa_blog.typee = 6 and sa_blog.state=0 "
                    + "INNER JOIN sa_evaluate_apply ON sa_evaluate_apply.id = sa_blog.ext_id and sa_evaluate_apply.evaluate_id= ?1 "
                    + "where sa_blog_comment.state=0 and sa_blog_comment.if_teacher_send=0 and (sa_blog_comment.user_id = ?2 or ?2 is null)"
                    + "GROUP BY sa_blog.user_id)A ")
    int getPostNumber(Long evaluateId, Long userId);

    @Query(nativeQuery = true, value = "SELECT COUNT(1) FROM sa_blog_grade "
            + "INNER JOIN sa_blog ON sa_blog_grade.blog_id=sa_blog.id and sa_blog.typee = 6 and sa_blog.state=0 "
            + "WHERE sa_blog_grade.state = 0 and sa_blog_grade.is_evaluate_jury = 1 "
            + " and sa_blog_grade.evaluate_id=:evaluateId and (sa_blog_grade.user_id=:userId or :userId is null) ")
    int getScoreNumber(@Param("evaluateId") Long evaluateId, @Param("userId") Long userId);

    @Modifying
    @Query(nativeQuery = true, value = "update sa_evaluate_jury set state=1 where teacher_id=?1 ")
    void deleteJudges(Long id);

}
